from sqlalchemy import and_, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import joinedload

from app.database.models import ProxyProvider, Server


async def get_providers(session: AsyncSession, proxy_type: str) -> list[ProxyProvider]:

    q = (
        select(Server)
        .where(Server.proxy_type == proxy_type)
        .options(joinedload(Server.provider))
    )

    res = await session.execute(q)

    return res.scalars().all()


async def query_providers(
    session: AsyncSession,
    proxy_type: str,
    proxy_privacy: str,
    country_id: int,
) -> list[ProxyProvider]:

    q = (
        select(ProxyProvider)
        .join(ProxyProvider.servers)
        .filter(
            and_(
                Server.status == "active",
                Server.proxy_type == proxy_type,
                Server.proxy_privacy == proxy_privacy,
                Server.country_id == country_id,
            )
        )
    )

    res = await session.execute(q)

    return res.scalars().unique().all()


async def get_provider(session: AsyncSession, provider_id: int) -> ProxyProvider:

    q = select(ProxyProvider).where(ProxyProvider.id == provider_id)
    res = await session.execute(q)

    return res.scalar()
